package com.itfreer.data.sqlite;

import android.content.Context;

import com.itfreer.data.general.ConnectionInfo;
import com.itfreer.data.general.DbValueConvertUtils;
import com.itfreer.data.general.Field;
import com.itfreer.data.general.GlobalDataType;
import com.itfreer.data.general.GlobalParameter;
import com.itfreer.data.general.IDataTypeConvert;
import com.itfreer.data.general.ITable;
import com.itfreer.data.general.ITableInfo;
import com.itfreer.data.general.RowBase;
import com.itfreer.data.general.TableInfoClass;
import com.itfreer.data.general.TableType;
import com.itfreer.utils.StringUtils;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

/**
 * 定义MYSQL数据处理类
 * @author chenxu
 *
 */
public class SqliteWorkspaceClass extends DatabaseWorkspaceBase {

	public SqliteWorkspaceClass(Context context, ConnectionInfo info) {
		super(context, info);
	}

	@Override
	protected String replaceSql(String sqlString) {
		if (StringUtils.IsNullOrSpace(sqlString))
			return sqlString;

		sqlString = sqlString.replace("[#COMBSTR#]", "||");
		sqlString = sqlString.replace("[", "").replace("]", "")
				.replace("@", ":").replace("#BDATE", "('")
				.replace("#EDATE", "')").replace("#EDATETIME", "')")
				.replace("#SYSDATETIME#", "sysdate");

		return sqlString;
	}

	/**
	 * 获取得到一个表的自动增值SQL
	 */
	@Override
	public String getAutoSql(ITable table, String fieldName) {
		return "0";
	}

	@Override
	public boolean fieldExist(String name, String fieldName) {
		name = name.trim().toUpperCase();
		fieldName = fieldName.trim().toUpperCase();
		String sql = "select count(COLUMN_NAME) as COUNTFIELDNAME from information_schema.COLUMNS where TABLE_NAME=@TABLE_NAME and COLUMN_NAME=@COLUMN_NAME";
		List<GlobalParameter> paraList = new ArrayList<GlobalParameter>();
		paraList.add(new GlobalParameter("@TABLE_NAME", name));
		paraList.add(new GlobalParameter("@COLUMN_NAME", fieldName));
		Object obj = getExecuteScalar(sql, paraList);
		long cmdresult;
		if (obj == null)
			cmdresult = 0;
		else
			cmdresult = Long.parseLong(obj.toString());
		if (cmdresult == 0)
			return false;
		else
			return true;
	}

	@Override
	public boolean objectExist(String objName) {
		if (StringUtils.IsNullOrSpace(objName)) {
			throw new RuntimeException("对象名称不能为空！");
		}
		objName = objName.trim().toUpperCase();
		String sql = "select count(table_name) as COUNTFIELDNAME from information_schema.TABLES where table_name = @tableName and TABLE_SCHEMA='"
				+ this.getDatabase()
				+ "' and (TABLE_TYPE='VIEW' or TABLE_TYPE='BASE TABLE')";
		List<GlobalParameter> paraList = new ArrayList<GlobalParameter>();
		paraList.add(new GlobalParameter("@tableName", objName));

		Object obj = getExecuteScalar(sql, paraList);
		long cmdresult;
		if (null == obj) {
			sql = "SELECT count(name) as COUNTFIELDNAME FROM ALL_SOURCE where name=@tableName";
			obj = getExecuteScalar(sql, paraList);
			if (null == obj)
				cmdresult = 0;
			else
				cmdresult = Long.parseLong(obj.toString());
		} else
			cmdresult = Long.parseLong(obj.toString());

		if (cmdresult == 0)
			return false;
		else
			return true;
	}

	@Override
	public TableType getObjectType(String objName) {
		if (StringUtils.IsNullOrSpace(objName))
			return TableType.UnDefine;

		objName = objName.toUpperCase();
		String sql = "select TABLE_TYPE from information_schema.TABLES where table_name=@viewName and TABLE_SCHEMA='"
				+ this.getDatabase() + "'";
		List<GlobalParameter> paraList = new ArrayList<GlobalParameter>();
		paraList.add(new GlobalParameter("@viewName", objName));
		Object obj = getExecuteScalar(sql, paraList);

		if (obj == null) {
			return TableType.UnDefine;
		} else {
			String typeStr = obj.toString().trim().toUpperCase();
			if (typeStr.equals("VIEW")) {
				return TableType.View;
			} else if (typeStr.equals("BASE TABLE")) {
				return TableType.Table;
			}
		}
		return TableType.UnDefine;
	}

	@Override
	public void deleteObject(TableType tableType, String objName) {
		String sql = "";

		switch (tableType) {
		case Table:
			sql = "drop table if exists " + objName;
			break;
		case View:
			tableType = getObjectType(objName);
			if (tableType == TableType.Table)
				sql = "drop materialized view if exists " + objName;
			else if (tableType == TableType.View)
				sql = "drop view if exists" + objName;
			break;
		case UnDefine:
		default:
			break;
		}

		if (!StringUtils.IsNullOrSpace(sql)){
            executeSql(sql, GlobalParameter.Empty);
		}
	}

	@Override
	public void rename(String oldTableName, String newTableName) {
		TableType tableType = getObjectType(oldTableName);
		if (tableType == TableType.Table) {
			String sql = "RENAME TABLE " + oldTableName + " to " + newTableName;
			executeSql(sql, GlobalParameter.Empty);
		}
	}

	@Override
	public void createTable(ITableInfo tableInfo, String tableName) {
		if (StringUtils.IsNullOrSpace(tableName))
			return;

		if (!objectExist(tableName)) {
			String sql = getMetadataSql(tableInfo, tableName);
			if (StringUtils.IsNullOrSpace(sql))
				return;
			executeSql(sql, GlobalParameter.Empty);
		}
	}

	/**
	 * 如果MetadataSql.CreateSql为空时， 根据其它信息，自动填充此属性
	 * 
	 * @param tableMetadata
	 * @param tableName
	 * @return
	 */
	protected String getMetadataSql(ITableInfo tableMetadata, String tableName) {
		if (tableMetadata.getTableType() == TableType.Table) {
			IDataTypeConvert dataTypeConvert = new SqliteDataTypeConvert();

			StringBuilder sqlBuilder = new StringBuilder();
			sqlBuilder.append("Create Table " + tableName + "(");
			Field[] allFields = tableMetadata.getFields();
			for (Field field : allFields) {
				sqlBuilder.append(field.getFieldName() + " ");
				if (field.getDataType() == GlobalDataType.String
						&& field.getMaxLen() > 0) {
					sqlBuilder.append(dataTypeConvert.Convert(
							field.getDataType()).replace("254",
							String.valueOf(field.getMaxLen())));
				} else if ((field.getDataType() == GlobalDataType.Int16
						|| field.getDataType() == GlobalDataType.Int32 || field
						.getDataType() == GlobalDataType.Int64)
						&& field.getMaxLen() > 0) {
					sqlBuilder.append(dataTypeConvert.Convert(field
							.getDataType()) + "(" + field.getMaxLen() + ")");
				} else {
					sqlBuilder.append(dataTypeConvert.Convert(field
							.getDataType()));
				}

				if (field.getIsAuto()) {
					sqlBuilder.append(" AUTO_INCREMENT ,");
				} else {
					sqlBuilder.append(" ,");
				}
			}

			// 添加选择数据库
			String sql = sqlBuilder.toString();

			Field[] autoFields = tableMetadata.getAutoFields();
			// 自动增值一定为主键
			if (autoFields != null && autoFields.length > 0) {
				String keySql = " PRIMARY KEY (" + autoFields[0].getFieldName()
						+ ") ";
				sql += keySql + ")";
			} else {
				// 主键
				Field[] keyFields = tableMetadata.getKeyNoAutoFields();
				if (keyFields.length > 0) {
					String keySql = " PRIMARY KEY (";
					for (Field field : keyFields) {
						keySql += field.getFieldName() + ",";
					}
					keySql = keySql.substring(0, keySql.length() - 1) + ")";
					sql += keySql + ")";
				} else {
					sql = sql.substring(0, (sql.length() - 1)) + ")";
				}
			}
			return sql;
		}
		return "";
	}

	@Override
	public void updateTable(ITableInfo tableInfo, String tableName) {
		if (StringUtils.IsNullOrSpace(tableName))
			return;

		TableType tableType = getObjectType(tableName);
		if (tableType == TableType.Table) {
			IDataTypeConvert dataTypeConvert = new SqliteDataTypeConvert();
			List<String> sqlList = new ArrayList<String>();
			for (Field field : tableInfo.getFields()) {
				String sql = "";
				String stringFieldType = "";

				if (field.getDataType() == GlobalDataType.String)
					stringFieldType = dataTypeConvert.Convert(
							field.getDataType()).replace("254",
							String.valueOf(field.getMaxLen()));
				else
					stringFieldType = dataTypeConvert.Convert(field
							.getDataType());

				if (fieldExist(tableName, field.getFieldName()))
					sql = "ALTER TABLE " + tableName + " alter column "
							+ field.getFieldName() + " " + stringFieldType;
				else
					sql = "ALTER TABLE " + tableName + " add column "
							+ field.getFieldName() + " " + stringFieldType;

				sqlList.add(sql);
			}
			for (String item : sqlList) {
				executeSql(item, GlobalParameter.Empty);
			}
		}
	}

	@Override
	public void createOrUpdateView(String viewName, String selectSql,
			boolean isStatic) {
		viewName = viewName.trim().toUpperCase();
		deleteObject(TableType.View, viewName);
		String sql = "";
		if (isStatic)
			sql = "CREATE materialized view "
					+ viewName
					+ " build immediate refresh force on demand START WITH SYSDATE NEXT SYSDATE + 1/12 as "
					+ selectSql;
		else
			sql = "CREATE View " + viewName + " as " + selectSql;
		executeSql(sql, GlobalParameter.Empty);
	}
}